package com.team.engine.user.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.context.annotation.Scope;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.team.engine.basedao.CommonDao;
import com.team.engine.util.SubPage;
import com.team.engine.util.SubPageOperate;

/**
 * TUser表dao操作类
 * @author MengLH
 * @data 2010-9-20
 */
@Repository
@Scope("singleton")
public class UserNavDaoImpl extends CommonDao<UserNav, Integer>  {

	public UserNavDaoImpl(){
		super.clazz = UserNav.class;
		super.className = "UserNav";
		super.idName = "userId";
	}
	
	/* (non-Javadoc)
	 * @see com.team.engine.user.dao.TUserDao#selectByName(java.lang.String)
	 */
	public List<UserNav> selectByName(String name){
		return this.getSession().createQuery("from UserNav where name = ?").setString(0, name).list();
	}
	
	public SubPage<UserNav> selectAllWithGroup(String page, String rowsCount){
		
		SubPageOperate<UserNav> sub = new SubPageOperate<UserNav>(page, rowsCount);
		SubPage<UserNav> query = sub.selectSubData(this.jdbcTemplate, JdbcMySQL.SELECT_ALL_WITH_GROUP, new UserMapper());
		return query;
	}
	
	private static final class UserMapper implements RowMapper<UserNav> {

		public UserNav mapRow(ResultSet rs, int arg1) throws SQLException {
			UserNav userNav = new UserNav();
			userNav.setUserId(rs.getString("user_id"));
			userNav.setName(rs.getString("name"));
			
			UserGroup userGroup = new UserGroup();
			userGroup.setNameNav(rs.getString("group_name"));
			userNav.setUsergroup(userGroup);
			
			return userNav;
		}
	}
	
	private static final class JdbcMySQL {
		
		public static final String SELECT_ALL_WITH_GROUP = 
			"SELECT u.user_id, u.name, t.usergroup_id"
			+ " , GROUP_CONCAT(DISTINCT t.name_nav ORDER BY t.parent_usergroup_id DESC SEPARATOR ',') AS group_name"
			+ " FROM user_nav u"
			+ " LEFT JOIN (" +
					"SELECT ugu.usergroup_id, ugu.user_id, ug.name_nav, ug.parent_usergroup_id" +
					" FROM user_group_user ugu, user_group ug WHERE ugu.usergroup_id = ug.usergroup_id" +
					") AS t"
			+ " ON u.user_id = t.user_id"
			+ " GROUP BY u.user_id";
	}
	
}
